﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using System.Dynamic;

namespace SQLWindow.SQLServerCE
{
    /// <summary>
    /// Логика взаимодействия для SqlWindow.xaml
    /// </summary>
    public partial class SqlWindow : Window
    {
        protected string[] SQL_DELIMITERS = new string[] { "\ngo", "\ngo\n", "\nGO", "\nGO\n" };
        protected SqlCeConnection conn;
        public SqlWindow()
        {
            InitializeComponent();
            try
            {
                conn = new SqlCeConnection(ConfigurationManager.ConnectionStrings["Database"].ConnectionString);
                conn.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        private void SetSqlInput(string text) 
        {
            SqlInput.Text = text;
        }

        private bool ExecuteSql(string text)
        {
            string[] commands = text.Split(SQL_DELIMITERS, StringSplitOptions.RemoveEmptyEntries);
            foreach (string _sql in commands)
            {
                string sql = _sql.Trim();
                if (sql.Length > 0)
                {

                    SqlCeCommand command = null;
                    SqlCeDataReader reader = null;

                    // Execute query
                    try
                    {
                        Console.WriteLine("SQL: " + sql);
                        command = new SqlCeCommand(sql, conn);
                        reader = command.ExecuteReader();
                        if (reader.RecordsAffected == -1)
                        {
                            UpdateColumns(ResultSet, reader);
                            ShowResult(ResultSet, reader);
                        }
                        else
                        {
                            Console.WriteLine(String.Format("{0} records affected", reader.RecordsAffected));
                        }
                    }
                    catch (Exception ex) 
                    {
                        Console.WriteLine("Не удалось выполнить запрос или еще что: " + ex.Message);
                    }
                }
            }
            return true;
        }

        private void UpdateColumns(DataGrid grid, IDataReader reader)
        {
            for (int i=0; i<reader.FieldCount; i++) {
                Console.Write(reader.GetName(i) + " | ");
            }
            Console.WriteLine();
        }

        private void ShowResult(DataGrid grid, IDataReader reader)
        {
            while (reader.Read())
            {
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    Console.Write(reader[i] + " | ");
                }
                Console.WriteLine();
            }
        }

        protected void AddToHistory(string text) 
        {
            foreach (ListBoxItem _item in SqlHistory.Items) 
            {
                if (text == (string)_item.Content)
                {
                    return;
                }
            }
            ListBoxItem item = new ListBoxItem();
            string itemName = text.Length > 32 ?  text.Substring(0, 32) : text;
            item.Content = text;
            SqlHistory.Items.Add(item);
        }

        private void ExecButton_Click(object sender, RoutedEventArgs e)
        {
            string sql = SqlInput.Text;
            if (ExecuteSql(sql))
            {
                AddToHistory(sql);
            }
        }

        private void ClearButton_Click(object sender, RoutedEventArgs e)
        {
            SetSqlInput("");
        }
    }
}
